package com.example.utils.db;

import com.example.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExecuteSQL {

    public static final Logger logger = LoggerFactory.getLogger(ExecuteSQL.class);

    /**
     * 删除表
     */
    public static boolean dropTable(Connection conn, String tableName) {
        try {
            conn.createStatement().executeUpdate("DROP TABLE " + tableName);
        } catch (SQLException e) {
            logger.info(tableName + "表删除失败!");
            e.printStackTrace();
            return false;
        }
        logger.info(tableName + "表删除成功!");
        return true;
    }

    public static boolean truncateTable(Connection conn, String tableName) {
        try {
            conn.createStatement().executeUpdate("TRUNCATE TABLE " + tableName);
        } catch (Exception e) {
            logger.info(tableName + "表记录删除失败!");
            e.printStackTrace();
            return false;
        }
        logger.info(tableName + "表记录删除成功!");
        return true;
    }

    /**
     * 更新
     */
    public static void executeUpdate(Connection conn, String sql) {
        try {
            conn.createStatement().executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接的所有表
     */
    public static StringBuffer getAllTables(Connection conn) {
        StringBuffer sb = new StringBuffer();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {// ///TABLE_TYPE/REMARKS
                sb.append("表名：").append(rs.getString("TABLE_NAME")).append(" ");
                sb.append("表类型：").append(rs.getString("TABLE_TYPE")).append(" ");
                sb.append("表所属数据库：").append(rs.getString("TABLE_CAT")).append(" ");
                sb.append("表所属用户名：").append(rs.getString("TABLE_SCHEM")).append(" ");
                sb.append("表备注：").append(rs.getString("REMARKS")).append("\n");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sb;
    }

    /**
     * 创建表
     *
     * @param sql 例:CREATE  TABLE  stock_info ( id CHAR(64) NOT NULL,stock_code CHAR(6), PRIMARY KEY (id))
     */
    public static void createTable(Connection conn, String sql) {
        ResultSet rs;
        String tableName = StringUtils.firstMatchedString(sql, "(?i)table\\s+[^\\s]+\\s");
        tableName = tableName.substring(5).trim().toUpperCase();
        DatabaseMetaData dMetaData;
        try {
            dMetaData = conn.getMetaData();
            rs = dMetaData.getTables(null, null, tableName, null);
            if (rs.next()) {
                System.out.println("已存在表:" + tableName);
                return;
            }
            conn.createStatement().executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("成功创建表:" + tableName);
    }

    /**
     * 获取单个结果字段,类型String
     */
    public static List<String> getListString(Connection conn, String sql) {
        logger.info("开始查询");
        ResultSet rs = null;
        List<String> list = new ArrayList<String>();
        try {
            rs = conn.createStatement().executeQuery(sql);
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取查询结果集,自定义key
     */
    public static List<Map<String, Object>> getListMapCustomKey(Connection conn, String sql, List<String> key) {
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Map<String, Object> m = new HashMap<String, Object>();
        int n = key.size();
        try {
            rs = conn.createStatement().executeQuery(sql);
            while (rs.next()) {
                for (int i = 0; i < n; i++) {
                    m.put(key.get(i), rs.getString(i + 1));
                }
                list.add(m);
                m = new HashMap<String, Object>();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取查询结果集,key为表名或别名
     */
    public static List<Map<String, String>> getListMap(Connection conn, String sql) {
        ResultSet rs = null;
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        Map<String, String> m = new HashMap<String, String>();
        try {
            rs = conn.createStatement().executeQuery(sql);
            ResultSetMetaData rsd = rs.getMetaData();
            int columnCount = rsd.getColumnCount();
            while (rs.next()) {
                for (int i = 0; i < columnCount; i++) {
                    m.put(rsd.getColumnLabel(i + 1), rs.getString(i + 1));
                }
                list.add(m);
                m = new HashMap<String, String>();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static int getTableCount(Connection conn, String tableName) {
        int count = 0;
        ResultSet rs = null;
        try {
            rs = conn.createStatement().executeQuery("select count(1) as count from " + tableName);
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 根据sql等到count，例：select count(1)  from STOCK_INFO
     */
    public static int getCount(Connection conn, String sql) {
        int count = 0;
        ResultSet rs = null;
        try {
            rs = conn.createStatement().executeQuery(sql);
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

}
